#-*- coding = utf-8 -*-
#@Time: 2020/4/11 12:59
#@Author: mx
#@File: ershou_to_db.py
#@Software: IntelliJ IDEA

#-*- coding = utf-8 -*-
#@Time: 2020/4/11 10:25
#@Author: mx
#@File: loupan_to_db.py
#@Software: IntelliJ IDEA

import os
import pymysql
from lib.utility.path import DATA_PATH
from lib.zone.city import *

import records
from lib.spider.base_spider import *

pymysql.install_as_MySQLdb()

class ErShouFangToMysql(BaseSpider):

    def ershoufang_data_to_db(self,city):
        logger.info("ershou to mysql start...")
        database = "mysql"
        db = None
        db = records.Database('mysql://root:sh@88861158@47.108.31.200:3308/house?charset=utf8', encoding='utf-8')
        #准备城市,日期
        # date = "20180331"   # 指定采集数据的日期
        # city = "sh"         # 指定采集数据的城市
        date = get_date_string()
        city_ch = get_chinese_city(city)
        csv_dir = "{0}/{1}/ershou/{2}/{3}".format(DATA_PATH,SPIDER_NAME,city,date)

        files = list()
        if not os.path.exists(csv_dir):
            logger.info("{0} does not exist.".format(csv_dir))
            logger.info("Please run 'python xiaoqu.py' firstly.")
            logger.info("Bye.")
            exit(0)
        else:
            logger.info('OK, start to process ' + get_chinese_city(city))
        for csv in os.listdir(csv_dir):
            data_csv = csv_dir + "/" + csv
            # print(data_csv)
            files.append(data_csv)

        # 清理数据
        count = 0
        row = 0
        col = 0
        for csv in files:
            with open(csv,'r') as f:
                for line in f:
                    count += 1
                    text = line.strip()
                    try:
                        # 如果小区名里面没有逗号，那么总共是4项
                        if text.count(',') == 9:
                            date, district, area,desc,total,info,pic1,pic2,pic3,pic4 = text.split(',')
                        elif text.count(',') < 9:
                            continue
                        elif text.count(',') == 10:
                            #处理房价和描述错位
                            fields = text.split(',')
                            date = fields[0]
                            district = fields[1]
                            area = fields[2]
                            desc = fields[3]+fields[4]
                            total = fields[5]
                            info = fields[6]
                            pic1 = fields[7]
                            pic2 = fields[8]
                            pic3 = fields[9]
                            pic4 = fields[10]
                        elif text.count(',') == 12:
                            #处理房价和描述错位
                            fields = text.split(',')
                            date = fields[0]
                            district = fields[1]
                            area = fields[2]
                            desc = fields[3]+fields[4]+fields[5]+fields[6]
                            total = fields[7]
                            info = fields[8]
                            pic1 = fields[9]
                            pic2 = fields[10]
                            pic3 = fields[11]
                            pic4 = fields[12]
                    except Exception as e:
                        logger.error(text)
                        logger.error(e)
                        continue
                    total = total.replace(r'万','')
                    total = float(total)
                    infos = info.split("|")
                    #如果信息包含5个信息
                    if len(infos) == 5:
                        storey = infos[0].strip()
                        buildtime = infos[1].strip()
                        layout = infos[2].strip()
                        size = infos[3].strip()
                        direction = infos[4].strip()
                    elif len(infos) == 4:
                        array0 = infos[0]
                        layout = array0.rstrip()[-4:]
                        storey = array0.split(' ')[0]
                        buildtime = ''
                        size = infos[1]
                        direction = infos[2]
                    pic = pic1.strip()+','+pic2.strip()+','+pic3.strip()+','+pic4.strip()
                    # logger.info("{0} {1} {2} {3}".format(date,district,area,total))
                    #写入数据库
                    db.query('INSERT INTO ershou(city,date,district,area,total,descript,storey,buildtime,layout,size,direction,pic) '
                             'VALUES(:city, :date, :district, :area, :total,:descript ,:storey,:buildtime,:layout,:size,:direction,:pic)',
                             city=city_ch,date=date,district=district,area=area,total=total,descript=desc,
                             buildtime=buildtime,layout=layout,size=size,direction=direction,storey=storey,pic=pic)
        logger.info("Total write {0} items to database.".format(count))
        logger.info("ershou to mysql end...")


if __name__ == "__main__":
    pass
    # city = 'cd'
    # spider = ErShouFangToMysql(SPIDER_NAME)
    # spider.ershoufang_data_to_db(city)